<?php
class Ckmodel extends CI_Model {

	function __construct()
	{
		parent::__construct();
	}

	public function init(){
		$dbHandle = $this->load->database('ckhandle', TRUE);
		if($dbHandle == ''){
			error_log('can not create db handle','ckhandle');
			echo (print_r($dbHandle,true));
		}
		return $dbHandle;
	}
	
	public function getTypeDeals($type, $storeUrl="Home",$start='0', $end='30'){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$ExpDate=date("Y-m-d",strtotime("-1 Months"));
		$query='';
		$query .= "select cpn.*, str.*, csct.* from deals_deal_tbl cpn, deals_store_tbl str, deals_store_deals_tbl csct where cpn.deals_type='$type'";
		if($type=='Deal' || $type=='Featured' || $type=='Top'){
			$query .= " and cpn.deals_expiredate>='$date' ";
		}else if ($type=='Expired'){
			$query .= " and cpn.deals_expiredate<'$date' and cpn.deals_expiredate>'$ExpDate' ";
		}
		if ($storeUrl!="Home"){
			$query .= " and str.store_url='$storeUrl' ";
		}
		$query .= " and cpn.deals_id=csct.deals_id and csct.store_id=str.store_id and cpn.deals_status='Yes' and str.store_status='Yes' order by cpn.deals_creationdate desc";
		if($start!='')
		{
			$query .=" limit $start, $end";
		}
		error_log("typecoupon query is>>>>>>> ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getRelatedDeals($dId, $type, $storeUrl="Home",$start='0', $end='30'){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$ExpDate=date("Y-m-d",strtotime("-1 Months"));
		$query='';
		$query .= "select cpn.*, str.*, csct.* from deals_deal_tbl cpn, deals_store_tbl str, deals_store_deals_tbl csct where cpn.deals_type='$type'";
		if($type=='Deal' || $type=='Featured' || $type=='Top'){
			$query .= " and cpn.deals_expiredate>='$date' ";
		}else if ($type=='Expired'){
			$query .= " and cpn.deals_expiredate<'$date' and cpn.deals_expiredate>'$ExpDate' ";
		}
		if($dId=='' && $storeUrl!="Home"){
			$query .= " and str.store_url!='$storeUrl'";
		}elseif ($storeUrl!="Home" && $dId!=''){
			$query .= " and str.store_url='$storeUrl' and cpn.deals_id!='$dId'";
		}
		$query .= " and cpn.deals_id=csct.deals_id and csct.store_id=str.store_id and cpn.deals_status='Yes' and str.store_status='Yes' order by cpn.deals_creationdate desc";
		if($start!='')
		{
			$query .=" limit $start, $end";
		}
		error_log("typecoupon query is>> ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getBanner(){
		$dbHandle = $this->init();
		$query = "SELECT *  FROM deals_home_banners_tbl hbt, deals_store_tbl st where hbt.home_banner_store=st.store_id and hbt.home_banner_status='Yes' order by hbt.home_banner_creationdate desc ";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$homeBanner = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$homeBanner[$i] = $row;
			$i++;
		}
		return $homeBanner;
	}
	
	public function getRandomBanners(){
		$dbHandle = $this->init();
		$query = "select * from deals_banners_tbl where banner_status='Yes' order by rand() limit 1";
		$result = mysql_query($query);
		$bannersArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$bannersArray[$i] = $row;
			$i++;
		}
		return $bannersArray;
	}
	
	public function getStoreBanners($store_id){
		$dbHandle = $this->init();
		$query = "select * from deals_banners_tbl where banner_store_id=$store_id and banner_status='Yes' order by banner_creationdate desc limit 1";
		$result = mysql_query($query);
		if(mysql_num_rows ($result) <= 0)
		{
			$query = "select * from deals_banners_tbl where banner_store_id=0 and banner_status='Yes' order by rand() limit 1";
			$result = mysql_query ($query);
		}
		$bannersArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$bannersArray[$i] = $row;
			$i++;
		}
		return $bannersArray;
	}
	
	public function getOtherDeals($category_id, $store_id, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, s.* from deals_deal_tbl c, deals_store_tbl s, deals_category_store_tbl cs, deals_store_deals_tbl sc where cs.store_id=s.store_id and s.store_id=sc.store_id and c.deals_id=sc.deals_id and sc.store_id!=$store_id ";
		if($category_id!=0){
			$query.=" and cs.category_id=$category_id and c.deals_category=$category_id ";
		}else{
			$query.=" and cs.category_id in(select cs.category_id from deals_category_store_tbl cs where cs.store_id=$store_id)";
		}
		$query.=" and c.deals_status='Yes' and c.deals_expiredate>='$date' order by rand() limit $limit";
		error_log("getTopCategoryCoupons query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}
	
	public function getDealDetail($dUrl){
		$dbHandle = $this->init();
		$query = "select * from deals_deal_tbl ddt, deals_store_tbl dst, deals_store_deals_tbl dsd where ddt.deals_url='$dUrl' and ddt.deals_id=dsd.deals_id and dst.store_id=dsd.store_id";
		error_log("get deal details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	
	public function getDealSpec($dId){
		$dbHandle = $this->init();
		$query = "select * from deals_specifications_tbl where spec_deal_id='$dId'";
		$result = mysql_query($query);
		$bannersArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$bannersArray[$i] = $row;
			$i++;
		}
		return $bannersArray;
	}
	
	public function getAffiliateURLByCouponId($deal_id, $store_id){
		$dbHandle = $this->init();
		$query = "SELECT deals_affilateurl from deals_deal_tbl where deals_id='$deal_id'";
		$result = mysql_query($query);
		$affArray = array();
		$affArray = mysql_fetch_assoc($result);
			
		if($affArray['deals_affilateurl']==''){
			$query1 = "SELECT store_affilateurl from deals_store_tbl where store_id='$store_id'";
			$result1 = mysql_query($query1);
			$affArray = mysql_fetch_assoc($result1);
		}
		return $affArray;
	}
	
	public function getStoreDetails($storeUrl){
		$dbHandle = $this->init();
		$query = "select * from deals_store_tbl cst, deals_category_store_tbl ccst where cst.store_id=ccst.store_id and cst.store_url='$storeUrl' and cst.store_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
	
	public function getStoreDeals($storeId){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select cpn.*, strCpn.*, str.* from deals_deal_tbl cpn, deals_store_tbl str, deals_store_deals_tbl strCpn where cpn.deals_id=strCpn.deals_id and strCpn.store_id=str.store_id and strCpn.store_id='$storeId' and cpn.deals_expiredate >=  '$date'";
		error_log("get store coupons query is ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	
	//----
	
	
	
	

	public function rateYourEndure($storeId, $ratingStar, $ipAddr){
		$dbHandle = $this->init();
		if($ratingStar!=0){
			$query = "select * from deals_ratings_tbl where store_id=$storeId and ip_address='$ipAddr'";
			error_log("insert ratings query ".$query);
			$result = mysql_query ($query);
			if(mysql_num_rows ($result) <= 0){
				$query = "INSERT INTO deals_ratings_tbl(rating_star, store_id, ip_address) values($ratingStar, $storeId, '$ipAddr')";
				error_log("insert ratings query ".$query);
				$result = mysql_query ($query);
			}
		}
		$query = "select count(*) as count, sum(rating_star) as ratings_total from deals_ratings_tbl where store_id=$storeId";
		error_log("get ratings query ".$query);
		$result = mysql_query ($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function contactUs($post){
		$dbHandle = $this->init();
		$query="INSERT INTO  deals_contactus_tbl(contact_name, contact_subject, contact_message, contact_email)VALUES ('".$post['contactName']."', '".$post['contactSubject']."', '".$post['contactMessage']."', '".$post['contactEmail']."')";
		error_log("query is ".$query);
		$result = $dbHandle->query($query);
	}


	function updateStoreViews($storeId, $month, $year){
		$query = "SELECT * FROM deals_storeviews_tbl WHERE storeviews_store_id = '$storeId' and views_month='$month' and views_year='$year'";
		$result = mysql_query ($query);
		if(mysql_num_rows ($result) >= 1)
		{
			$update = "UPDATE deals_storeviews_tbl SET store_views = (store_views + 1) WHERE storeviews_store_id = '$storeId' and views_month='$month' and views_year='$year'";
			$result = mysql_query ($update);
		} else {
			$insert = "INSERT INTO deals_storeviews_tbl(storeviews_store_id, store_views, views_month, views_year) VALUES ('".$storeId."', '1', '$month', '$year')";
			$result = mysql_query ($insert);
		}
		$update = "UPDATE deals_storeviews_tbl SET `store_total_views` = (SELECT SUM(x.`store_views`) FROM (SELECT * FROM deals_storeviews_tbl) AS x WHERE x.`storeviews_store_id` =  deals_storeviews_tbl.`storeviews_store_id`) where storeviews_store_id='$storeId'";
		$result = mysql_query ($update);
		return true;
	}

	public function getPageDetails($type, $start, $count){
		$dbHandle = $this->init();
		$query="Select * from deals_page_tbl where page_status='Yes' limit $start, $count";
		error_log("query is....". $query);
		$result= mysql_query($query);
		$pageArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$pageArray[$i] = $row;
			$i++;
		}
		return $pageArray;
	}

	public function getAllCoupons(){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_coupons_tbl ORDER BY coupon_name";
		$result = mysql_query($query);
		$couponArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponArray[$i] = $row;
			$i++;
		}
		return $couponArray;
	}

	public function getAllStores(){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
		FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sc
		WHERE c.coupon_id = sc.coupon_id
		AND s.store_id = sc.store_id AND s.store_status='Yes'
		AND c.coupon_expiredate >=  '$date'
		AND c.coupon_status='Yes'
		GROUP BY s.store_id union
		SELECT s.*, 0 AS noOfCoupons
		FROM deals_store_tbl s, deals_store_coupon_tbl sc
		WHERE s.store_id != sc.store_id AND s.store_status='Yes'
		GROUP BY s.store_id) as temp
		group by temp.store_id ORDER BY temp.store_name ASC";
		error_log("query is ".$query);
		$result = mysql_query($query);
		$alphaStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$alphaStoresArray[$i] = $row;
			$i++;
		}
		return $alphaStoresArray;
	}


	public function getAllBanks(){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_banks_tbl where bank_status='Yes' ORDER BY bank_name ASC";
		$result = mysql_query($query);
		$allBanksArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$allBanksArray[$i] = $row;
			$i++;
		}
		return $allBanksArray;
	}

	public function getBankDetailsByURL($bankUrl){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_banks_tbl where bank_status='Yes' and bank_url='$bankUrl'";
		$result = mysql_query($query);
		$allBanksArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$allBanksArray[$i] = $row;
			$i++;
		}
		return $allBanksArray;
	}
	 

	public function getCouponStores($coupon_id){
		$dbHandle = $this->init();
		$query = "SELECT store_id FROM deals_store_coupon_tbl where coupon_id='$coupon_id'";
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			array_push($couponStoresArray, $row['store_id']);
			$i++;
		}
		return $couponStoresArray;
	}

	public function getAllCategories(){
		$dbHandle = $this->init();
		$query = "select c.*, cs.store_count from deals_category_tbl c, (select count(store_id) as store_count, category_id from deals_category_store_tbl group by category_id) cs where c.category_id=cs.category_id group by category_id order by c.category_name asc";
		$result = mysql_query($query);
		$categoriesArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoriesArray[$i] = $row;
			$i++;
		}
		return $categoriesArray;
	}

	public function getStoreDetailsById($storeId){
		$dbHandle = $this->init();
		$query = "select * from deals_store_tbl cst, deals_category_store_tbl ccst where cst.store_id=ccst.store_id and cst.store_id='$storeId' and cst.store_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}

	public function getCategoryDetails($catUrl){
		$dbHandle = $this->init();
		$query = "select * from deals_category_tbl where category_url='$catUrl' and category_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$categoryDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoryDetailsArray[$i] = $row;
			$i++;
		}
		return $categoryDetailsArray;
	}


	public function getSimilarOffers($category_id){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select * from deals_coupons_tbl c, deals_store_tbl s where s.store_category_tbl_id=$category_id and c.coupon_type='Offer' and s.store_type='Money' and c.coupon_expiredate>='$date' group by s.store_id order by rand()";
		error_log("query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}

	public function getTopCategoryCoupons($category_id, $store_id, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, s.* from deals_coupons_tbl c, deals_store_tbl s, deals_category_store_tbl cs, deals_store_coupon_tbl sc where cs.store_id=s.store_id and c.coupon_type!='Offer' and s.store_id=sc.store_id and c.coupon_id=sc.coupon_id and sc.store_id!=$store_id and s.store_type='Money'";
		if($category_id!=0){
			$query.=" and cs.category_id=$category_id and c.coupon_category=$category_id ";
		}else{
			$query.=" and cs.category_id in(select cs.category_id from deals_category_store_tbl cs where cs.store_id=$store_id)";
		}
		$query.=" and c.coupon_status='Yes' and c.coupon_expiredate>='$date' order by rand() limit $limit";
		error_log("getTopCategoryCoupons query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}

	public function getStoreCoupons($storeId){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select cpn.*, strCpn.*, str.* from deals_coupons_tbl cpn, deals_store_tbl str, deals_store_coupon_tbl strCpn where cpn.coupon_id=strCpn.coupon_id and strCpn.store_id=str.store_id and strCpn.store_id='$storeId' and cpn.coupon_expiredate >=  '$date'";
		error_log("get store coupons query is ".$query);
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}

	public function getTopCategoryOffers($category_id, $store_id, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select c.*, s.* from deals_coupons_tbl c, deals_store_tbl s, deals_category_store_tbl cs, deals_store_coupon_tbl sc where cs.store_id=s.store_id and c.coupon_type='Offer' and s.store_id=sc.store_id and c.coupon_id=sc.coupon_id and sc.store_id!=$store_id and s.store_type='Money'";
		if($category_id!=0){
			$query.=" and cs.category_id=$category_id and c.coupon_category=$category_id ";
		}else{
			$query.=" and cs.category_id in(select cs.category_id from deals_category_store_tbl cs where cs.store_id=$store_id)";
		}
		$query.=" and c.coupon_status='Yes' and c.coupon_expiredate>='$date' order by rand() limit $limit";
		error_log("getTopCategoryCoupons query is ".$query);
		$result = mysql_query($query);
		$catCouponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$catCouponsArray[$i] = $row;
			$i++;
		}
		return $catCouponsArray;
	}

	public function getAlphaStores($alpha){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		if($alpha=='0-9'){
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
			FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sc
			WHERE c.coupon_id = sc.coupon_id
			AND s.store_id = sc.store_id
			AND c.coupon_expiredate >=  '$date'
			AND c.coupon_status='Yes'
			AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '0' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '1' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '2' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '3' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '4' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '5' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '6' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '7' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '8' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '9')
			GROUP BY s.store_id union
			SELECT s.*, 0 AS noOfCoupons
			FROM deals_store_tbl s, deals_store_coupon_tbl sc
			WHERE s.store_id != sc.store_id
			AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '0' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '1' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '2' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '3' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '4' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '5' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '6' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '7' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '8' OR LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '9')
			GROUP BY s.store_id) as temp
			group by temp.store_id ORDER BY temp.store_order ASC";
		} elseif($alpha!=''){
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sc
WHERE c.coupon_id = sc.coupon_id
AND s.store_id = sc.store_id
AND c.coupon_expiredate >=  '$date'
AND c.coupon_status='Yes'
AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '$alpha')
GROUP BY s.store_id union
SELECT s.*, 0 AS noOfCoupons
FROM deals_store_tbl s, deals_store_coupon_tbl sc
WHERE s.store_id != sc.store_id
AND (LEFT(SUBSTRING_INDEX(s.store_name, ' ', 1), 1) = '$alpha')
GROUP BY s.store_id) as temp where temp.store_status='Yes'
group by temp.store_id ORDER BY temp.store_order ASC";
		} else {
			$query = "select temp.* from (SELECT s.*, COUNT( * ) AS noOfCoupons
FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sc
WHERE c.coupon_id = sc.coupon_id
AND s.store_id = sc.store_id
AND c.coupon_expiredate >=  '$date'
AND c.coupon_status='Yes'
GROUP BY s.store_id union
SELECT s.*, 0 AS noOfCoupons
FROM deals_store_tbl s, deals_store_coupon_tbl sc
WHERE s.store_id != sc.store_id
GROUP BY s.store_id) as temp where temp.store_status='Yes'
group by temp.store_id ORDER BY temp.store_order ASC";
		}
		error_log("Query is ".$query);
		$result = mysql_query($query);
		$alphaStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$alphaStoresArray[$i] = $row;
			$i++;
		}
		return $alphaStoresArray;
	}



public function getCategoryStores($categoryId, $limit='16'){
		$dbHandle = $this->init();
		if($categoryId=='popular'){
			$query = "SELECT * FROM deals_store_tbl ORDER BY store_order ASC limit 16";
		}else{
			$query = "SELECT distinct cst.* FROM deals_store_tbl cst, deals_category_store_tbl ccst where cst.store_id=ccst.store_id and ccst.category_id='$categoryId' ORDER BY store_order ASC limit $limit";
		}
		$result = mysql_query($query);
		$alphaStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$alphaStoresArray[$i] = $row;
			$i++;
		}
		return $alphaStoresArray;
	}



	public function searchStore($tag){
		$dbHandle = $this->init();
		$searchStoresArray = array();
		$query="SELECT group_concat(distinct store_id) as store_id FROM deals_tags_tbl WHERE store_tag LIKE '%$tag%' GROUP BY store_id";
		//error_log("query is ".$query);
		$result = $dbHandle->query($query);
		foreach($result->result() as $row){
			$ids=$row->store_id;
		}
		if (isset($ids) && $ids!=""){
			$query="SELECT * FROM deals_store_tbl WHERE store_status='Yes' AND store_id IN($ids)";
			$result = mysql_query($query);
			$i=0;
			while($row = mysql_fetch_assoc($result)){
				$searchStoresArray[$i] = $row;
				$i++;
			}
		}
		return $searchStoresArray;
	}

	public function subscribeEmailId($emailId, $storeId,$storeName){
		$dbHandle = $this->init();
		$subscribeId=-1;
		$emailid=urldecode($emailId);
		$queryCmd = "insert into deals_subscribe_tbl (subscribe_emailid, subscribe_store_id,subscribe_store_name) values('".addslashes($emailid)."', '".addslashes($storeId)."', '".addslashes($storeName)."')";
		$query = $dbHandle->query($queryCmd);	
		error_log("queryCmd is ", $queryCmd);;
		$subscribeId=$dbHandle->insert_id();
		return $subscribeId;
	}
	
	
	public function getBankOfferByStore($store_id){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*, b.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct, deals_banks_tbl b  WHERE b.bank_id=c.coupon_bank and c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND sct.store_id!='$store_id' AND c.coupon_bank!=0 AND c.coupon_expiredate>= curdate() and c.coupon_status='Yes' group by c.coupon_bank ORDER BY rand()";
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}

	public function getAllBankCoupon($bankId){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type!='Offer' AND c.coupon_bank='$bankId' AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() ORDER BY rand()";
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}

	public function getAllBankOffer($bankId){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_bank='$bankId' AND c.coupon_status='Yes' AND c.coupon_expiredate>= curdate() ORDER BY rand()";
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}


	public function getOffersWithoutBank(){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_bank=0 AND c.coupon_expiredate>= curdate() ORDER BY rand() limit 0, 10";
		$result = mysql_query($query);
		$couponArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponArray[$i] = $row;
			$i++;
		}
		return $couponArray;
	}

	public function getRelatedBankCoupons($bankId, $limit){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type!='Offer' AND c.coupon_bank!='$bankId' and c.coupon_bank!=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() GROUP BY c.coupon_bank ORDER BY rand() limit $limit";
		error_log('similar bank query is'.$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}

	public function getRelatedBankOffers($bankId, $limit){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_bank!='$bankId' and c.coupon_bank!=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() GROUP BY c.coupon_bank ORDER BY rand() limit $limit";
		error_log('similar bank offres query is>>>>>>>>>'.$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}

	public function submitCoupon($a,$b,$c,$d){
		$dbHandle = $this->init();
		$query="insert into deals_coupons_tbl (coupon_name, coupon_description, coupon_code, coupon_type, coupon_status) values('$b','$d','$c','Shared','No')";
		$result = mysql_query($query);
		$inserId = mysql_insert_id();
		$query1 = "insert into deals_store_coupon_tbl (store_id, coupon_id) values('$a','$inserId')";
		$result1 = mysql_query($query1);
	}

	public function addAdvertisers($post){
		$dbHandle = $this->init();
		$query = "INSERT INTO deals_advertiser_tbl(advertiser_name, advertiser_email, advertiser_phno, advertiser_company, advertiser_url, advertiser_affiliate_prgm, advertiser_status) VALUES ('".addslashes($post['advertiserName'])."','".addslashes($post['advertiserEmail'])."','".addslashes($post['advertiserPhno'])."','".addslashes($post['advertiserCompany'])."','".addslashes($post['advertiserUrl'])."','".addslashes($post['advertiserAff'])."','Yes') ";
		$result = mysql_query($query);
	}

	public function getStoreVideos($sId){
		$dbHandle = $this->init();
		$query = "select * from deals_videos_tbl where video_store_id='$sId'";
		$result = mysql_query($query);
		$couponArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponArray[$i] = $row;
			$i++;
		}
		return $couponArray;
	}

	public function getOccasionDetails($oUrl){
		$dbHandle = $this->init();
		$query = "select * from deals_occasions_tbl where occasion_url='$oUrl'";
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getSimilarOccasion($oId){
		$dbHandle = $this->init();
		$query = "select * from deals_occasions_tbl where occasion_id!='$oId' and occasion_status='Yes' order by occasion_name";
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getAllOccasion(){
		$dbHandle = $this->init();
		$query = "select * from deals_occasions_tbl where occasion_status='Yes' order by occasion_name";
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getAllOccasionCoupons($occasion_id){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type!='Offer' AND c.coupon_occasion='$occasion_id' AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() ORDER BY rand()";
		error_log('occasion coupons>>>>>>'.$query);
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getAllOccasionOffers($occasion_id){
		$dbHandle = $this->init();
		$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_occasion='$occasion_id' AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() ORDER BY rand()";
		error_log('occasion offers>>>>>>'.$query);
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getRelatedOccasionCoupons($occasion_id, $limit){
		$dbHandle = $this->init();
		if($occasion_id==''){
			$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type!='Offer' AND c.coupon_occasion !=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() group by c.coupon_occasion ORDER BY rand() limit $limit";
		}else{
			$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type!='Offer' AND c.coupon_occasion!='$occasion_id' AND c.coupon_occasion !=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() group by c.coupon_occasion ORDER BY rand() limit $limit";
		}
		error_log('occasion related coupons>>>>>>'.$query);
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getRelatedOccasionOffers($occasion_id, $limit){
		$dbHandle = $this->init();
		if($occasion_id==''){
			$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_occasion !=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() group by c.coupon_occasion ORDER BY rand() limit $limit";
		}else{
			$query = "SELECT c.*, s.*, sct.*  FROM deals_coupons_tbl c, deals_store_tbl s, deals_store_coupon_tbl sct WHERE c.coupon_id=sct.coupon_id AND sct.store_id=s.store_id AND c.coupon_type='Offer' AND c.coupon_occasion!='$occasion_id' AND c.coupon_occasion !=0 AND c.coupon_status='Yes' and c.coupon_expiredate>= curdate() group by c.coupon_occasion ORDER BY rand() limit $limit";
		}
		error_log('occasion related offers>>>>>>'.$query);
		$result = mysql_query($query);
		$resultArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$resultArray[$i] = $row;
			$i++;
		}
		return $resultArray;
	}

	public function getBreadcrumCat($cat){
		$dbHandle = $this->init();
		$query = "select * from deals_category_tbl where category_id='$cat' and category_status='Yes'";
		error_log("get cat details query is ".$query);
		$result = mysql_query($query);
		$categoryDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$categoryDetailsArray[$i] = $row;
			$i++;
		}
		return $categoryDetailsArray;
	}

	public function getAllBanksById($bankId){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_banks_tbl where bank_id!='$bankId' and bank_status='Yes' ORDER BY bank_name ASC";
		$result = mysql_query($query);
		$allBanksArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$allBanksArray[$i] = $row;
			$i++;
		}
		return $allBanksArray;
	}

	public function getCouponDetail($coupon_url){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_coupons_tbl cc, deals_store_tbl cs, deals_store_coupon_tbl csc where cc.coupon_url='$coupon_url' and cc.coupon_id=csc.coupon_id and cs.store_id=csc.store_id";
		error_log($query);
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponStoresArray[$i]= $row;
			$i++;
		}
		return $couponStoresArray;
	}

	public function getTopCoupons($coupon_url='', $limit='400'){
		$dbHandle = $this->init();
		if($coupon_url!=''){
			$query = "SELECT * FROM deals_coupons_tbl cc, deals_store_tbl cs, deals_store_coupon_tbl csc where cc.coupon_url!='$coupon_url' and cc.coupon_url!='' and cc.coupon_id=csc.coupon_id and cs.store_id=csc.store_id and cc.coupon_status='Yes' and cc.coupon_expiredate>=CURDATE() limit 5";
		}else{
			$query = "SELECT * FROM deals_coupons_tbl cc, deals_store_tbl cs, deals_store_coupon_tbl csc where cc.coupon_url!='' and cc.coupon_id=csc.coupon_id and cs.store_id=csc.store_id and cc.coupon_status='Yes' and cc.coupon_expiredate>=CURDATE() order by cc.coupon_creationdate desc limit $limit";
		}
		error_log($query);
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponStoresArray[$i]= $row;
			$i++;
		}
		return $couponStoresArray;
	}

	public function getStoreSimilarCoupon($coupon_url, $store_id){
		$dbHandle = $this->init();
		$query = "SELECT * FROM deals_coupons_tbl cc, deals_store_tbl cs, deals_store_coupon_tbl csc where cc.coupon_url!='$coupon_url' and cs.store_id='$store_id' and cc.coupon_id=csc.coupon_id and cs.store_id=csc.store_id and cc.coupon_status='Yes' order by cc.coupon_creationdate desc limit 10";
		error_log($query);
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponStoresArray[$i]= $row;
			$i++;
		}
		return $couponStoresArray;
	}
	
	public function getStoreDealCoupons($storeId, $limit){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select cpn.*, strCpn.*, str.* from deals_coupons_tbl cpn, deals_store_tbl str, deals_store_coupon_tbl strCpn where cpn.coupon_id=strCpn.coupon_id and strCpn.store_id=str.store_id and strCpn.store_id='$storeId' and cpn.coupon_type='FeaturedCpn' and cpn.coupon_expiredate >=  '$date' order  by cpn.coupon_creationdate desc limit 0, $limit";
		//error_log("get store coupons query is ".$query);
		$result = mysql_query($query);
		if(mysql_num_rows($result)==0)
		{
			$query = "select cpn.*, strCpn.*, str.* from deals_coupons_tbl cpn, deals_store_tbl str, deals_store_coupon_tbl strCpn where cpn.coupon_id=strCpn.coupon_id and strCpn.store_id=str.store_id and strCpn.store_id='$storeId' and cpn.coupon_type!='FeaturedCpn' and cpn.coupon_expiredate >=  '$date' order  by cpn.coupon_creationdate desc limit 0, $limit";
			//error_log("get store coupons query is ".$query);
			$result = mysql_query($query);
		}
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getPopupOffer(){
		$dbHandle = $this->init();
		$date=date('Y-m-d');
		$query = "select * from deals_popupoffer_tbl where offer_status='Yes' order by RAND() limit 1";
		$result = mysql_query($query);
		$couponsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponsArray[$i] = $row;
			$i++;
		}
		return $couponsArray;
	}
	
	public function getTopStoresDeals(){
		$dbHandle = $this->init();
		$query = "SELECT csd.*, cst.store_name,cst.store_image FROM deals_storedeals_tbl csd, deals_store_tbl cst where csd.store_id=cst.store_id order by cst.store_order desc limit 0, 16";
		$result = mysql_query($query);
		$alphaStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$alphaStoresArray[$i] = $row;
			$i++;
		}
		return $alphaStoresArray;
	}
	
	public function getTopDealsCoupons($keys, $limit){
		$dbHandle = $this->init();
		$Ids = '';
		foreach($keys as $temp){
			$Ids .= $temp['store_id'].',';
		}
		$query = "SELECT * FROM deals_coupons_tbl cc, deals_store_tbl cs, deals_store_coupon_tbl csc where cc.coupon_id=csc.coupon_id and cs.store_id=csc.store_id and cs.store_id IN(".trim($Ids, ",").") and cc.coupon_status='Yes' and cc.coupon_expiredate>=CURDATE() GROUP BY cs.store_id order by cc.coupon_creationdate desc limit $limit";
		//error_log('>>>>>>>>>>>>>>>>> '.$query);
		$result = mysql_query($query);
		$couponStoresArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$couponStoresArray[$i]= $row;
			$i++;
		}
		return $couponStoresArray;
	}
	
	public function getStoreSearchDetails($storeUrl){
		$dbHandle = $this->init();
		$query = "select * from deals_store_tbl cst, deals_category_store_tbl ccst where cst.store_id=ccst.store_id and cst.store_url like '%$storeUrl' and cst.store_status='Yes'";
		error_log("get store details query is ".$query);
		$result = mysql_query($query);
		$storeDetailsArray = array();
		$i=0;
		while($row = mysql_fetch_assoc($result)){
			$storeDetailsArray[$i] = $row;
			$i++;
		}
		return $storeDetailsArray;
	}
}
?>